package service;

import dao.BaseDao;
import eneity.News;
import eneity.Topic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserManage {
    BaseDao baseDao=new BaseDao();
    Connection connection=null;
    PreparedStatement pstm=null;
    ResultSet rs=null;
    String sql="";
    public List<Topic> getTopic(){
        connection=baseDao.getConnByJNDI();
        List<Topic> list=new ArrayList<Topic>();
        sql="SELECT tid,tname FROM topic;";
        pstm=baseDao.pstmt(connection,sql,null);
        try {
            rs=pstm.executeQuery();
            while (rs.next()){
                Topic topic=new Topic();
                topic.setTypaName(rs.getString(2));
                topic.setTid(rs.getString(1));
                list.add(topic);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return null;
    }//取得主题

    public  List<News> getNews(int pageindex,int nid){
        connection=baseDao.getConnByJNDI();
        List<News> list=new ArrayList<News>();
        Object [] objs={nid};
        sql="SELECT  topic.tname,news.ntitle,news.ncreateDate,news.nauthor,news.nsummary,news.ncontent,news.nid,news.ntid FROM news INNER JOIN topic ON topic.tid=news.ntid WHERE news.nid=?;";
        pstm=baseDao.pstmt(connection,sql,objs);
        try {
            rs=pstm.executeQuery();
            while (rs.next()){
                News news=new News();
                news.setTname(rs.getString(1));
                news.setNtitle(rs.getString(2));
                news.setNcreatdate(rs.getString(3));
                news.setNauthor(rs.getString(4));
                news.setNssmmary(rs.getString(5));
                news.setNcontent(rs.getString(6));
                news.setNid(rs.getString(7));
                news.setNtid(rs.getString(8));
                list.add(news);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return null;
    }//取得新闻

    public  List<News> getNews(int pageindex){
        connection=baseDao.getConnByJNDI();
        List<News> list=new ArrayList<News>();
        Object [] objs={pageindex};
        sql="SELECT  topic.tname,news.ntitle,news.ncreateDate,news.nauthor,news.nsummary,news.ncontent,news.nid,news.ntid FROM news INNER JOIN topic ON topic.tid=news.ntid LIMIT ? ,10;";
        pstm=baseDao.pstmt(connection,sql,objs);
        try {
            rs=pstm.executeQuery();
            while (rs.next()){
                News news=new News();
                news.setTname(rs.getString(1));
                news.setNtitle(rs.getString(2));
                news.setNcreatdate(rs.getString(3));
                news.setNauthor(rs.getString(4));
                news.setNssmmary(rs.getString(5));
                news.setNcontent(rs.getString(6));
                news.setNid(rs.getString(7));
                news.setNtid(rs.getString(8));
                list.add(news);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return null;
    }//取得新闻

    public int addTopic(Topic topic){
        connection=baseDao.getConnByJNDI();
        sql="INSERT INTO topic VALUES(DEFAULT,?);";
        Object[] objects={topic.getTypaName()};
        try {
            int result = baseDao.executeUpdate(connection,sql,objects);
            if (result>0){
                return result;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return -1;
    }//添加主题

    public int editNews(News news){
        connection=baseDao.getConnByJNDI();
        sql="UPDATE news SET ntid=? ,ntitle=?,nauthor=?,ncontent=?,nsummary=? WHERE nid=?;";
        Object[] objects={news.getNtid(),news.getNtitle(),news.getNauthor(),news.getNcontent(),news.getNssmmary(),news.getNid()};
        try {
            int result = baseDao.executeUpdate(connection,sql,objects);
            if (result>0){
                return result;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return -1;
    }//编辑新闻

    public List<News>gettNewsComments(int newsId){
        connection=baseDao.getConnByJNDI();
        List<News> list=new ArrayList<News>();
        sql="SELECT comments.ccontent,comments.cauthor FROM news INNER JOIN comments ON comments.cnid=news.nid WHERE nid=?;";
        Object [] objs={newsId};
        pstm=baseDao.pstmt(connection,sql,objs);
        try {
            rs=pstm.executeQuery();
            while (rs.next()){
                News news=new News();
                news.setNewsComments(rs.getString(1));
                news.setNauthor(rs.getString(2));
                list.add(news);
            }
            if (list.size()==0){
                News news=new News();
                news.setNewsComments("暂时没有评论~~");
                news.setNauthor("");
                list.add(news);
                return list;
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return null;
    }//取得新闻的评论

    public int addComments(News news){
        connection=baseDao.getConnByJNDI();
        sql="INSERT INTO comments VALUES(DEFAULT,?,?,NOW(),'127.0.0.1','大聪明');";
        Object[] objects={news.getNid(),news.getNewsComments()};
        try {
            int result=baseDao.executeUpdate(connection,sql,objects);
            if (result>0){
                return result;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return -1;
    }
    public int addNews(News news){
        connection=baseDao.getConnByJNDI();
        sql="INSERT INTO news VALUES(DEFAULT,?,?,?,NOW(),?,?,?);";
        Object[] objects={news.getNtid(),news.getNtitle(),news.getNauthor(),news.getNid(),news.getNcontent(),news.getNssmmary()};
        try {
            return baseDao.executeUpdate(connection,sql,objects);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            baseDao.cloesAll(connection,pstm,rs);
        }
        return -1;
    }
}
